<?php
define("VIDEO_WHAT", "distinct(videoID), url, description, duration, name");
define("ALBUM_WHAT", "distinct(albumID), name, cover, description");
define("ARTIST_WHAT", "distinct(artistID), name, description");
define("GENRE_WHAT", "distinct(genreID), name, description");
define("USER_WHAT", "distinct(facebookID)");

require_once './src/LoginClass.php';
require_once './src/ErrorClass.php';
  
Class DBClass {
  var $login;
  
  public function DBClass() {
    $this->login = new LoginClass();
  }

  private function makeRequest($request) {
    //pripojeni k db
    if (!$connection = mysql_connect('81.2.197.7', 'j4w-youtube-user','77o2geJM7cgZ637')) {
      return new ErrorClass(MYSQL_CONNECT);
    };
    if (!$dbSelect = mysql_select_db('j4w-youtube-library', $connection)) {
      return new ErrorClass(MYSQL_DB_SELECT);
    };
    if (!$dbSetNames = mysql_query('SET NAMES \'utf8\'')) {
      return new ErrorClass(MYSQL_UTF8);
    };
    
    //odeslani dotazu
    if (!($resultId = mysql_query($request))) {
      //neni vysledek
      return new ErrorClass(MYSQL_NO_RESULT);
    } else {
      //nacteni vysledku
      $result = array();
      while ($dbResultItem = @mysql_fetch_object($resultId)) {
        array_push($result, $dbResultItem);
      }
      
      return $result;

      mysql_free_result($resultId);
      mysql_close($connection);
    }

    return new ErrorClass(UNIVERSAL);
  }
  
  private function makeLoggedRequest($request) {
    $logged = $this->login->isLogged();
    
    if ($logged) {
      if (gettype($logged) <> "boolean"){
        return $logged;
      } else {
        return $this->makeRequest($request);
      }
    } else {
      //uzivatel neni prihlasen
      return new ErrorClass(NOT_LOGGED);
    }
  }

  public function isIdInDB($id) {
    $request = "SELECT " . USER_WHAT . " FROM User WHERE facebookID = '" . $id . "' LIMIT 1";
    $result = $this->makeRequest($request);
    if (is_array($result) && reset($result)->facebookID == $id) {
      return TRUE;
    } else if ($result->errorCode == MYSQL_NO_RESULT) {
      return FALSE;
    } else {
      return $result;
    }
  }
  
  public function writeUserId($id) {
    if ($this->isIdInDB($id) == false) {
      $request = "INSERT INTO User VALUES ('" . $id . "')";
      $result = $this->makeRequest($request);
      if (is_array($result)) {
        return true;
      } else {
        return $result;
      }
    } else {
      return new ErrorClass(USER_EXISTS);
    }
  }
  
  private function changeType($arr, $type) {
    if (is_array($arr)) {
      foreach ($arr as $value) {
      	$value->_explicitType = $type;
      }
    }
    return $arr;
  }
  
  private function makeLoggedRequestAsType($request, $type) {
    $result = $this->makeLoggedRequest($request);
    return $this->changeType($result, $type);
  }
                                              
//_______________________________________VIDEO__________________________________
  private function getVideosWithAllData ($request) {
    $result = $this->makeLoggedRequestAsType($request, "Video");
    foreach ($result as $value) {
    	$value->albums = $this->getAllAlbumsByVideoId($value->videoID);
    	$value->artists = $this->getAllArtistsByVideoId($value->videoID);
    	$value->genres = $this->getAllGenresByVideoId($value->videoID);
    }
    return $result;
  }
  
  public function getAllVideos($offset, $limit) {
    $request = "SELECT " . VIDEO_WHAT . " FROM Video LIMIT " . $limit . " OFFSET " . $offset;
    return $this->getVideosWithAllData($request);
  }
  
  public function getVideoById($id) {
    $request = "SELECT " . VIDEO_WHAT . " FROM Video WHERE videoID = '" . $id . "' LIMIT 1";
    return $this->getVideosWithAllData($request);
  }
  
  public function getAllVideosByAlbumId($id) {
    $request = "SELECT " . VIDEO_WHAT . " FROM Video NATURAL JOIN VideoAlbum WHERE albumID = '" . $id . "'";
    return $this->getVideosWithAllData($request);
  }
  
  public function getAllVideosByArtistId($id, $offset, $limit) {
    $request = "SELECT " . VIDEO_WHAT . " FROM Video NATURAL JOIN VideoArtist WHERE artistID = '" . $id . "' LIMIT " . $limit . " OFFSET " . $offset;
    return $this->getVideosWithAllData($request);
  }
  
  public function getAllVideosByGenreId($id, $offset, $limit) {
    $request = "SELECT " . VIDEO_WHAT . " FROM Video NATURAL JOIN VideoGenre WHERE genreID = '" . $id . "' LIMIT " . $limit . " OFFSET " . $offset;
    return $this->getVideosWithAllData($request);
  }

//_______________________________________ALBUM__________________________________
  public function getAllAlbums($offset, $limit) {
    $request = "SELECT " . ALBUM_WHAT . " FROM Album LIMIT " . $limit . " OFFSET " . $offset;
    return $this->makeLoggedRequestAsType($request, "Album");
  }
  
  public function getAlbumById($id) {
    $request = "SELECT " . ALBUM_WHAT . " FROM Album WHERE albumID = '" . $id . "' LIMIT 1";
    return $this->makeLoggedRequestAsType($request, "Album");
  }
  
  public function getAllAlbumsByVideoId($id) {
    $request = "SELECT " . ALBUM_WHAT . " FROM Album NATURAL JOIN VideoAlbum WHERE videoID = '" . $id . "'";
    return $this->makeLoggedRequestAsType($request, "Album");
  }
  
  public function getAllAlbumsByArtistId($id, $offset, $limit) {
    $request = "SELECT " . ALBUM_WHAT . " FROM Album NATURAL JOIN VideoAlbum NATURAL JOIN VideoArtist WHERE artistID = '" . $id . "' LIMIT " . $limit . " OFFSET " . $offset;
    return $this->makeLoggedRequestAsType($request, "Album");
  }
  
  public function getAllAlbumsByGenreId($id, $offset, $limit) {
    $request = "SELECT " . ALBUM_WHAT . " FROM Album NATURAL JOIN VideoAlbum NATURAL JOIN VideoGenre WHERE genreID = '" . $id . "' LIMIT " . $limit . " OFFSET " . $offset;
    return $this->makeLoggedRequestAsType($request, "Album");
  } 

//______________________________________Artist__________________________________  
  public function getAllArtists($offset, $limit) {
    $request = "SELECT " . ARTIST_WHAT . " FROM Artist LIMIT " . $limit . " OFFSET " . $offset;
    return $this->makeLoggedRequestAsType($request, "Artist");
  }
  
  public function getArtistById($id) {
    $request = "SELECT " . ARTIST_WHAT . " FROM Artist WHERE artistID = '" . $id . "' LIMIT 1";
    return $this->makeLoggedRequestAsType($request, "Artist");
  }
  
  public function getAllArtistsByVideoId($id) {
    $request = "SELECT " . ARTIST_WHAT . " FROM Artist NATURAL JOIN VideoArtist WHERE videoID = '" . $id . "'";
    return $this->makeLoggedRequestAsType($request, "Artist");
  }
  
  public function getAllArtistsByAlbumId($id, $offset, $limit) {
    $request = "SELECT " . ARTIST_WHAT . " FROM Artist NATURAL JOIN VideoArtist NATURAL JOIN VideoAlbum WHERE albumID = '" . $id . "'";
    return $this->makeLoggedRequestAsType($request, "Artist");
  }
  
  public function getAllArtistsByGenreId($id, $offset, $limit) {
    $request = "SELECT " . ARTIST_WHAT . " FROM Artist NATURAL JOIN VideoArtist NATURAL JOIN VideoGenre WHERE genreID = '" . $id . "'";
    return $this->makeLoggedRequestAsType($request, "Artist");
  }

//_____________________________________Genre____________________________________  
  public function getAllGenres($offset, $limit) {
    $request = "SELECT " . GENRE_WHAT . " FROM Genre LIMIT " . $limit . " OFFSET " . $offset;
    return $this->makeLoggedRequestAsType($request, "Genre");
  }
  
  public function getGenreById($id) {
    $request = "SELECT " . GENRE_WHAT . " FROM Genre WHERE genreID = '" . $id . "' LIMIT 1";
    return $this->makeLoggedRequestAsType($request, "Genre");
  }
  
  public function getAllGenresByVideoId($id) {
    $request = "SELECT " . GENRE_WHAT . " FROM Genre NATURAL JOIN VideoGenre WHERE videoID = '" . $id . "'";
    return $this->makeLoggedRequestAsType($request, "Genre");
  }
  
  public function getAllGenresByAlbumId ($id, $offset, $limit) {
    $request = "SELECT " . GENRE_WHAT . " FROM Genre NATURAL JOIN VideoGenre NATURAL JOIN VideoAlbum WHERE albumID = '" . $id . "'";
    return $this->makeLoggedRequestAsType($request, "Genre");
  }
  
  public function getAllGenresByArtistId ($id, $offset, $limit) {
    $request = "SELECT " . GENRE_WHAT . " FROM Genre NATURAL JOIN VideoGenre NATURAL JOIN VideoArtist WHERE artistID = '" . $id . "'";
    return $this->makeLoggedRequestAsType($request, "Genre");
  }
}
?>
